﻿/*== VSLT_prc_GetDatabaseInfo =============================================================================
* Gets core info about the database configuration and the machine specs for
* the system housing the results repository.
* EXAMPLE:
EXEC	VSLT_prc_GetDatabaseInfo
		@DatabaseName = N'LoadTest2010'
===============================================================================*/
	--START CODE--
	CREATE PROCEDURE VSLT_prc_GetDatabaseInfo
		@DatabaseName NVARCHAR(255)
	AS
	BEGIN
		-- Get Database Version
		DECLARE @cmptlevel tinyint
		SELECT @cmptlevel = (SELECT cmptlevel from master.dbo.sysdatabases WHERE name = 'master')

		-- Get Recovery Model
		DECLARE @RecoveryModel NVARCHAR(255)
		SELECT @RecoveryModel = (SELECT CONVERT(NVARCHAR(255), DATABASEPROPERTYEX(name, 'Recovery')) AS 'Recovery Model' 
		FROM   master.dbo.sysdatabases WHERE name = @DatabaseName)

		-- Get number of cores
		DECLARE @CpuCount INT
		SELECT @CpuCount = (SELECT cpu_count FROM sys.dm_os_sys_info)

		-- Build the proper query for getting memory (column name changed in SQL 2012), Also add the results of
		-- the above queries so we get all info in a single resultset
		DECLARE @stmt nvarchar(MAX)
		IF(@cmptlevel = 110 OR @cmptlevel = 120) BEGIN
			SELECT @stmt = N'SELECT ' + CONVERT(NVARCHAR(255),@cmptlevel) + ' AS Version, '''
			+ @RecoveryModel + ''' AS [Recovery Model], ' 
			+ CONVERT(NVARCHAR(255),@CpuCount) 
			+ ' AS [Num Cores], (physical_memory_kb / 1048576) AS [Memory in GB] FROM sys.dm_os_sys_info' END
		ELSE BEGIN
			SELECT @stmt = N'SELECT ' + CONVERT(NVARCHAR(255),@cmptlevel) + ' AS Version, '''
			+ @RecoveryModel + ''' AS [Recovery Model], ' 
			+ CONVERT(NVARCHAR(255),@CpuCount) 
			+ ' AS [Num Cores], (physical_memory_in_bytes / 1073741824) AS [Memory in GB] FROM sys.dm_os_sys_info' END

		-- Get the info
		exec sp_executesql @stmt

		-- Get the Disk Info
		EXEC master.dbo.xp_fixeddrives

		-- Get the stats on the Load Test DB
		EXEC sp_helpdb @DatabaseName

		-- Get the stats on the Temp DB
		EXEC sp_helpdb tempdb

	END
	GO
	--START CODE--
	GRANT EXECUTE ON VSLT_prc_GetDatabaseInfo TO PUBLIC
	GO

/*== VSLT_prc_Get_TSL_Reporting_Version ===================================================================
* Returns the version of the TSL stored procedure set that is currently
* installed in the repository. This is used to see if the latest version
* of the procedures is installed.
===============================================================================*/
	--START CODE--
	CREATE PROCEDURE VSLT_prc_Get_TSL_Reporting_Version
	AS
	BEGIN
		DECLARE @Version nvarchar(25)
		----- SET THE VERSION NUMBER HERE -----
		----- This should match exactly the version number in the reporting tool's config file
		SET @Version = '5.2.5.1 Beta'
		SELECT @Version
	END
	GO
	--START CODE--
	GRANT EXECUTE ON VSLT_prc_Get_TSL_Reporting_Version TO PUBLIC
	GO

	/*== VSLT_prc_BiggestTablesByRows =========================================================================
* Returns a list of the largest five tables in the database based on the
* total number of rows in each table
===============================================================================*/
	--START CODE--
	CREATE PROCEDURE VSLT_prc_BiggestTablesByRows
	AS
	BEGIN
		SELECT TOP 5 object_name(p.object_id) AS 'table name', CAST(SUM(rows) AS INT) AS [total rows] 
		FROM sys.partitions AS p
		INNER JOIN sys.objects AS o
			ON p.object_id = o.object_id
		WHERE o.type = 'U' and p.index_id in (0,1)
		GROUP BY p.object_id 
		ORDER BY [total rows] DESC
	END
	GO
	--START CODE--
	GRANT EXECUTE ON VSLT_prc_BiggestTablesByRows TO PUBLIC
	GO


/*== VSLT_prc_UpdateLoadTestCoreInfoTable =========================================================================
===============================================================================*/
	--START CODE--
	CREATE PROCEDURE VSLT_prc_UpdateLoadTestCoreInfoTable AS	
	BEGIN
		---------------------------------------------------------------------------------
		DECLARE @RunId INT
		DECLARE @Qty INT -- value used at the end for parsing validation.
		DECLARE @Duration INT -- value used at the end for parsing validation.
		DECLARE myCursor CURSOR FOR
			SELECT LoadTestRunId FROM LoadTestRun 
			WHERE Outcome != 'InProgress'
			AND LoadTestRunId NOT IN (SELECT LoadTestRunId FROM TSL_LoadTestCoreInfo)
			
		OPEN myCursor
		FETCH NEXT FROM myCursor INTO @RunId
		WHILE @@FETCH_STATUS = 0
			BEGIN	
				---------------------------------------------------------------------------------
				-- Core Summary Info
				---------------------------------------------------------------------------------
				INSERT INTO TSL_LoadTestCoreInfo (LoadTestRunId)
					SELECT @RunId
				
				-- Set the parsing failed flag to TRUE immediately. Set it to false after all parsing completes
				UPDATE TSL_LoadTestCoreInfo	SET FLAG_LoadTestParsingFailed = 1 WHERE LoadTestRunId = @RunId

				---------------------------------------------------------------------------------
				-- Agent count and max user count
				---------------------------------------------------------------------------------
				UPDATE TSL_LoadTestCoreInfo	SET	AgentCount = (SELECT COUNT(AgentId) FROM LoadTestRunAgent WHERE LoadTestRunId = @RunId) WHERE LoadTestRunId = @RunId
				UPDATE TSL_LoadTestCoreInfo	SET	MaxUsers = (SELECT TOP 1 ComputedValue FROM LoadTestComputedCounterSample WHERE LoadTestRunId = @RunId 
					AND CategoryName = 'LoadTest:Scenario' AND CounterName = 'User Load' AND InstanceName = '_Total' ORDER BY ComputedValue DESC) WHERE LoadTestRunId = @RunId
				UPDATE TSL_LoadTestCoreInfo	SET	SampleInterval = (SELECT DATEDIFF(second,IntervalStartTime ,IntervalEndTime) FROM LoadTestRunInterval 
					WHERE TestRunIntervalId = 1 AND LoadTestRunId = @RunId) WHERE LoadTestRunId = @RunId
				---------------------------------------------------------------------------------
				-- Test Count and Avg test time
				---------------------------------------------------------------------------------
				BEGIN

					UPDATE TSL_LoadTestCoreInfo	SET TotalTests = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Test'
						AND CounterName = 'Total Tests' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE TSL_LoadTestCoreInfo	SET PassedTests = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Test'
						AND CounterName = 'Passed Tests' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE TSL_LoadTestCoreInfo	SET FailedTests = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Test'
						AND CounterName = 'Failed Tests' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE TSL_LoadTestCoreInfo	SET TestsSec_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Test'
						AND CounterName = 'Tests/Sec' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE TSL_LoadTestCoreInfo	SET AvgTT_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Test'
						AND CounterName = 'Avg. Test Time' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId
				END

				---------------------------------------------------------------------------------
				-- Transactional Count and Avg trans times
				---------------------------------------------------------------------------------
				BEGIN
				
					UPDATE TSL_LoadTestCoreInfo	SET TotalTrans = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Transaction'
						AND CounterName = 'Total Transactions' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE TSL_LoadTestCoreInfo	SET TransSec_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Transaction'
						AND CounterName = 'Transactions/Sec' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE TSL_LoadTestCoreInfo	SET AvgTrET_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Transaction'
						AND CounterName = 'Avg. Transaction Time' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE TSL_LoadTestCoreInfo	SET AvgTrRT_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Transaction'
						AND CounterName = 'Avg. Response Time' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

				END

				---------------------------------------------------------------------------------
				-- Page Count and Avg Page times
				---------------------------------------------------------------------------------
				BEGIN
					UPDATE TSL_LoadTestCoreInfo	SET TotalPages = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Page'
						AND CounterName = 'Total Pages' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE TSL_LoadTestCoreInfo	SET PagesSec_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Page'
						AND CounterName = 'Pages/Sec' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE TSL_LoadTestCoreInfo	SET AvgPgT_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Page'
						AND CounterName = 'Avg. Page Time' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId
				END
				
				---------------------------------------------------------------------------------
				-- Request Count and Avg Request times
				---------------------------------------------------------------------------------
				BEGIN
					UPDATE TSL_LoadTestCoreInfo	SET TotalRequests = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Request'
						AND CounterName = 'Total Requests' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE TSL_LoadTestCoreInfo	SET PassedRequests = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Request'
						AND CounterName = 'Passed Requests' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE TSL_LoadTestCoreInfo	SET FailedRequests = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Request'
						AND CounterName = 'Failed Requests' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE TSL_LoadTestCoreInfo	SET CachedRequests = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Request'
						AND CounterName = 'Cached Requests' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE TSL_LoadTestCoreInfo	SET ReqSec_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Request'
						AND CounterName = 'Requests/Sec' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE TSL_LoadTestCoreInfo	SET AvgReqT_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Request'
						AND CounterName = 'Avg. Response Time' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE TSL_LoadTestCoreInfo	SET AvgReqLen_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Request'
						AND CounterName = 'Avg. Content Length' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId
				END

				---------------------------------------------------------------------------------
				-- Test Rig efficiency counters
				---------------------------------------------------------------------------------
				BEGIN
					UPDATE TSL_LoadTestCoreInfo	SET PctTime_WebTest = (SELECT CAST(AVG(ComputedValue) AS Decimal(15,2)) FROM LoadTestComputedCounterSample	
						WHERE CategoryName = 'LoadTest:Test' AND CounterName = '% Time in WebTest code' AND InstanceName = '_Total' 
						AND LoadTestRunId = @RunId) WHERE LoadTestRunId = @RunId
							
					UPDATE TSL_LoadTestCoreInfo	SET PctTime_Rules = (SELECT CAST(AVG(ComputedValue) AS Decimal(15,2)) FROM LoadTestComputedCounterSample	
						WHERE CategoryName = 'LoadTest:Test' AND CounterName = '% Time in Rules' AND InstanceName = '_Total' 
						AND LoadTestRunId = @RunId) WHERE LoadTestRunId = @RunId
							
					UPDATE TSL_LoadTestCoreInfo	SET PctTime_LoadPlugin = (SELECT CAST(AVG(ComputedValue) AS Decimal(15,2)) FROM LoadTestComputedCounterSample	
						WHERE CategoryName = 'LoadTest:Test' AND CounterName = '% Time in LoadTestPlugin' AND InstanceName = '_Total' 
						AND LoadTestRunId = @RunId) WHERE LoadTestRunId = @RunId
				END			

				---------------------------------------------------------------------------------
				-- Flags
				---------------------------------------------------------------------------------
				BEGIN
					UPDATE TSL_LoadTestCoreInfo	SET FLAG_ContainsPages = (CASE (SELECT COUNT(PageId) FROM LoadTestPageSummaryData WHERE LoadTestRunId = @RunId)
							WHEN 0 THEN 0 ELSE 1 END) WHERE LoadTestRunId = @RunId
			
					UPDATE TSL_LoadTestCoreInfo	SET FLAG_ContainsTransactions = (CASE (SELECT COUNT(TransactionId) FROM LoadTestTransactionSummaryData  WHERE LoadTestRunId = @RunId)
							WHEN 0 THEN 0 ELSE 1 END) WHERE LoadTestRunId = @RunId
			
					UPDATE TSL_LoadTestCoreInfo	SET FLAG_ContainsDetailedTimingInfo = (CASE (SELECT COUNT(TestDetailId) FROM LoadTestTestDetail WHERE LoadTestRunId = @RunId)
							WHEN 0 THEN 0 ELSE 1 END) WHERE LoadTestRunId = @RunId
			
					UPDATE TSL_LoadTestCoreInfo	SET FLAG_TestAborted = (CASE (SELECT Outcome FROM LoadTestRun WHERE LoadTestRunId = @RunId)
							WHEN 'Completed' THEN 0 ELSE 1 END) WHERE LoadTestRunId = @RunId

					UPDATE TSL_LoadTestCoreInfo	SET FLAG_ContainsStepLoadProfile = (CASE 
								(SELECT COUNT(DISTINCT ComputedValue) FROM LoadTestPerformanceCounterSample AS a
								INNER JOIN MTSL_View_PerfmonInstanceNamesAndIds AS b
								ON a.LoadTestRunId = b.LoadTestRunId
								AND a.InstanceId = b.InstanceId
								WHERE a.LoadTestRunId = @RunId AND b.CounterName = 'User Load' 
								AND b.Instance = '_Total' AND a.ComputedValue <> 0)
							WHEN 1 THEN 0 ELSE 1 END) WHERE LoadTestRunId = @RunId
			
					-- Parsing completed for the run, so do a final sanity check on the results, looking for a total test Qty > 0 OR a Duration > 0.
					-- If either are zero or NULL, then the results will be invalid. If not, then we're good to go.
					SET @Qty = (SELECT TotalTests FROM TSL_LoadTestCoreInfo WHERE LoadTestRunId = @RunId)
					SET @Duration = (SELECT RunDuration FROM LoadTestRun WHERE LoadTestRunId = @RunId)
					IF (@Qty IS NOT NULL AND @Qty > 0 AND @Duration > 0) 
					BEGIN
						UPDATE TSL_LoadTestCoreInfo	SET FLAG_LoadTestParsingFailed = (0) WHERE LoadTestRunId = @RunId
					END

				END

				---------------------------------------------------------------------------------
				-- Add a row to the logging table
				---------------------------------------------------------------------------------
				INSERT INTO TSL_DbChangeLogs (LoadTestRunId, ChangeTypeId, ChangeTime) VALUES (@RunId, 1, GetDate()) 

			FETCH NEXT FROM myCursor INTO @RunId
		END
		CLOSE myCursor
		DEALLOCATE myCursor

		EXEC VSLT_prc_UpdateScoreCardMachineAvailableMBTable
		EXEC VSLT_prc_UpdateScoreCardMachineCpuTable
	END
	GO
	--START CODE--
	GRANT EXECUTE ON VSLT_prc_UpdateLoadTestCoreInfoTable TO PUBLIC
	GO


/*== VSLT_prc_UpdateScoreCardMachineAvailableMBTable =========================================================================
===============================================================================*/
	--START CODE--
	CREATE PROCEDURE VSLT_prc_UpdateScoreCardMachineAvailableMBTable AS	
	BEGIN
		---------------------------------------------------------------------------------
		DECLARE @RunId INT
		DECLARE myCursor CURSOR FOR
			SELECT LoadTestRunId FROM LoadTestRun 
			WHERE LoadTestRunId NOT IN (SELECT DISTINCT LoadTestRunId FROM TSL_ScoreCardMachineAvailableMB)
			AND Outcome != 'In Progress'
			
		OPEN myCursor
		FETCH NEXT FROM myCursor INTO @RunId
		WHILE @@FETCH_STATUS = 0
			BEGIN	
				---------------------------------------------------------------------------------
				-- Core Summary Info
				---------------------------------------------------------------------------------
				INSERT INTO TSL_ScoreCardMachineAvailableMB
					SELECT 
						a.LoadTestRunId 
						,a.MachineName
						,b.InstanceId
						,COUNT(a.ComputedValue) AS [Qty]
						,CAST(MIN(a.ComputedValue) AS Decimal(15,2)) AS [Min]
						,CAST(AVG(a.ComputedValue) AS Decimal(15,2)) AS [Avg]
						,CAST(MAX(a.ComputedValue) AS Decimal(15,2)) AS [Max]
					FROM LoadTestComputedCounterSample AS a
					INNER JOIN MTSL_View_PerfmonInstanceNamesAndIds AS b 
					ON a.LoadTestRunId = b.LoadTestRunId
						AND a.MachineName = b.MachineName 
						AND a.CategoryName = b.CategoryName
						AND a.CounterName = b.CounterName 
						WHERE a.CategoryName = 'Memory' 
						AND a.CounterName = 'Available MBytes' 
						AND a.InstanceName = 'systemdiagnosticsperfcounterlibsingleinstance'
						AND a.LoadTestRunId = @RunId
					GROUP BY a.LoadTestRunId, a.MachineName, b.InstanceId
					ORDER BY a.MachineName ASC

				---------------------------------------------------------------------------------
				-- Add a row to the logging table
				---------------------------------------------------------------------------------
				INSERT INTO TSL_DbChangeLogs (LoadTestRunId, ChangeTypeId, ChangeTime) VALUES (@RunId, 3, GetDate()) 

			FETCH NEXT FROM myCursor INTO @RunId
		END
		CLOSE myCursor
		DEALLOCATE myCursor
	END
	GO
	--START CODE--
	GRANT EXECUTE ON VSLT_prc_UpdateScoreCardMachineAvailableMBTable TO PUBLIC
	GO


/*== VSLT_prc_UpdateScoreCardMachineCpuTable =========================================================================
===============================================================================*/
	--START CODE--
	CREATE PROCEDURE VSLT_prc_UpdateScoreCardMachineCpuTable AS	
	BEGIN
		---------------------------------------------------------------------------------
		DECLARE @RunId INT
		DECLARE myCursor CURSOR FOR
			SELECT LoadTestRunId FROM LoadTestRun 
			WHERE LoadTestRunId NOT IN (SELECT DISTINCT LoadTestRunId FROM TSL_ScoreCardMachineCPU)
			AND Outcome != 'In Progress'
			
		OPEN myCursor
		FETCH NEXT FROM myCursor INTO @RunId
		WHILE @@FETCH_STATUS = 0
			BEGIN	
				---------------------------------------------------------------------------------
				-- Core Summary Info
				---------------------------------------------------------------------------------
				
				INSERT INTO TSL_ScoreCardMachineCPU
					SELECT 
						a.LoadTestRunId 
						,a.MachineName
						,b.InstanceId
						,COUNT(a.ComputedValue) AS [Qty]
						,CAST(MIN(a.ComputedValue) AS Decimal(15,2)) AS [Min]
						,CAST(AVG(a.ComputedValue) AS Decimal(15,2)) AS [Avg]
						,CAST(MAX(a.ComputedValue) AS Decimal(15,2)) AS [Max]
					FROM LoadTestComputedCounterSample AS a
					INNER JOIN MTSL_View_PerfmonInstanceNamesAndIds AS b 
					ON a.LoadTestRunId = b.LoadTestRunId
						AND a.MachineName = b.MachineName 
						AND a.CategoryName = b.CategoryName
						AND a.CounterName = b.CounterName 
						AND a.InstanceName = b.Instance
					WHERE a.CategoryName = 'Processor' 
						AND a.CounterName = '% Processor Time' 
						AND a.InstanceName = '_Total'
						AND a.LoadTestRunId = @RunId
					GROUP BY a.LoadTestRunId, a.MachineName, b.InstanceId
					ORDER BY a.MachineName ASC

				---------------------------------------------------------------------------------
				-- Add a row to the logging table
				---------------------------------------------------------------------------------
				INSERT INTO TSL_DbChangeLogs (LoadTestRunId, ChangeTypeId, ChangeTime) VALUES (@RunId, 2, GetDate()) 

			FETCH NEXT FROM myCursor INTO @RunId
		END
		CLOSE myCursor
		DEALLOCATE myCursor
	END
	GO
	--START CODE--
	GRANT EXECUTE ON VSLT_prc_UpdateScoreCardMachineCpuTable TO PUBLIC
	GO

/*== VSLT_prc_Update_TSL_LoadTestInterval_Table =================================
*/
	--START CODE--
	CREATE PROCEDURE VSLT_prc_Update_TSL_LoadTestInterval_Table
		@RunIds nvarchar(1024)
		,@ComparisonColumn nvarchar(25) = 'Average'
	AS
	BEGIN
		WITH C1 AS
			(SELECT 
				LoadTestRunId
				, IntervalEndTime
				, ComputedValue
				, CASE 
					WHEN ComputedValue <> coalesce(LAG(ComputedValue) OVER(ORDER BY IntervalEndTime ASC),-1) THEN 1 
					ELSE null END AS IsGroupStart
			FROM LoadTestComputedCounterSample
			WHERE 
				CounterName = 'User Load' 
				AND InstanceName = '_Total')
		,C2 AS
			(SELECT 
				LoadTestRunId
				, IntervalEndTime
				, ComputedValue
				,  COUNT(IsGroupStart) OVER(ORDER BY IntervalEndTime ROWS UNBOUNDED PRECEDING) AS [Group]
			FROM C1)

		INSERT INTO TSL_LoadTestIntervals (LoadTestRunId, StartOffset, EndOffset, UserLoad, NumberOfDataPoints)
			SELECT 
				C2.LoadTestRunId
				, DATEDIFF(second, run.StartTime, MIN(IntervalEndTime)) AS [IntervalStart]
				, DATEDIFF(second, run.StartTime, MAX(IntervalEndTime)) AS [IntervalEnd]
				, MIN(ComputedValue) AS ComputedValue
				, Count(*) AS NumberOfPoints
			FROM C2
			INNER JOIN LoadTestRun AS run ON C2.LoadTestRunId = run.LoadTestRunId

			WHERE C2.LoadTestRunId NOT IN (SELECT DISTINCT LoadTestRunId FROM TSL_LoadTestIntervals)
			GROUP BY C2.LoadTestRunId, [Group], run.StartTime
			having count(*) >= 10;
	END
	GO
	--START CODE--
	GRANT EXECUTE ON VSLT_prc_Update_TSL_LoadTestInterval_Table TO PUBLIC 
	GO

